package cn.yunhe.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.yunhe.entity.ProvienceMessage;
import cn.yunhe.util.DButil;

public class ProvienceMessageDaoImpl implements IProvienceMessageDao {
	Connection con = null;
	PreparedStatement ps = null;
	ResultSet rs = null;


	public List<ProvienceMessage> searchProvienceMessage(String wri,int pagesize,int currpage) {
		
		List<ProvienceMessage> list = new ArrayList<ProvienceMessage>();
		
		
		try {
			con = DButil.getCon();
			
			String sql = "select p_name,p_date,p_cont,p_one,p_two from (select t.*,rownum rn from (select * from province_message) t where rownum <= ? and p_wri = "
					+ "'"+wri+"') where rn >= ?";
			ps = con.prepareStatement(sql);
			ps.setInt(1, pagesize*currpage);
			ps.setInt(2,pagesize*(currpage-1)+1);
			rs = ps.executeQuery();
			while(rs.next()){
				ProvienceMessage proviencemessage = new ProvienceMessage();
				proviencemessage.setP_name(rs.getString("p_name"));
				proviencemessage.setP_date(rs.getString("p_date"));
				proviencemessage.setP_cont(rs.getString("p_cont"));
				proviencemessage.setP_one(rs.getInt("p_one"));
				proviencemessage.setP_two(rs.getInt("p_two"));
				
				list.add(proviencemessage);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DButil.close(con, ps, rs);
		}
		return list;
	}



	public int getCount(String wri) {
		int count = 0;
		try {
			con = DButil.getCon();
			String sql = "select count(p_name) from province_message where p_wri = '"+wri+"'";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs.next()){
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			DButil.close(con, ps, rs);
		}
		return count;
	}



	@Override
	public ProvienceMessage selectProById(int pid) {
		// TODO Auto-generated method stub
		ProvienceMessage proviencemessage = null;
		con=DButil.getCon();
		String sql ="select * from province_message where p_id = ?";
		try {
			ps=con.prepareStatement(sql);
			ps.setInt(1, pid);
			rs=ps.executeQuery();
			if(rs.next()){
				proviencemessage = new ProvienceMessage();
				proviencemessage.setP_name(rs.getString("p_name"));
				proviencemessage.setP_date(rs.getString("p_date"));
				proviencemessage.setP_cont(rs.getString("p_cont"));
				proviencemessage.setP_one(rs.getInt("p_one"));
				proviencemessage.setP_two(rs.getInt("p_two"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DButil.close(con, ps, rs);
		}
		return proviencemessage;
	}



	@Override
	public List<ProvienceMessage> getAll() {
		List<ProvienceMessage> list = new ArrayList<ProvienceMessage>();
		try {
			con = DButil.getCon();
			
			String sql = "select * from province_message";
			ps = con.prepareStatement(sql);
			
			rs = ps.executeQuery();
			while(rs.next()){
				ProvienceMessage proviencemessage = new ProvienceMessage();
				proviencemessage.setP_id(rs.getInt("p_id"));
				proviencemessage.setP_name(rs.getString("p_name"));
				proviencemessage.setP_date(rs.getString("p_date"));
				proviencemessage.setP_cont(rs.getString("p_cont"));
				proviencemessage.setP_one(rs.getInt("p_one"));
				proviencemessage.setP_two(rs.getInt("p_two"));
				proviencemessage.setP_wri(rs.getString("p_wri"));
				list.add(proviencemessage);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DButil.close(con, ps, rs);
		}
		return list;
		}
	
	public int inOrDelOrUp(String sql, Object[] pramas) {
		return DButil.insertOrDelOrUpdate(sql, pramas);
	}

}
